--    Program: 		CAPARIMP_CUSTOMER_PARTY.sql
--    Description:  Import AR CUSTOMER PARTY information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_party_id				number;
   l_party_number			varchar2(30);
                    		                 		
   cursor cur_ar_customer_party is
      select * from CRST_HZ_PARTIES
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_party loop   	
     l_err_msg_tmp := '';
     
     select HZ_PARTIES_S.NEXTVAL 
     INTO l_party_id
     FROM DUAL;
     /*
     select HZ_PARTY_NUMBER_S.NEXTVAL 
     INTO l_party_number
     FROM DUAL;
     */
     update HZ_PARTIES
     set    party_number = substr(party_number||'X', 1, 30)
     where  party_id = cur.party_id;     
     
     update CRST_HZ_PARTIES
     set    party_id_n = l_party_id,
            party_number_n = cur.party_number
     where  party_id = cur.party_id;        
     
     begin
       -- import data
       insert into HZ_PARTIES
       (
          PARTY_ID                    ,
          PARTY_NUMBER                ,
          PARTY_NAME                  ,
          PARTY_TYPE                  ,
          VALIDATED_FLAG              ,
          LAST_UPDATED_BY             ,
          CREATION_DATE               ,
          LAST_UPDATE_LOGIN           ,
          REQUEST_ID                  ,
          PROGRAM_APPLICATION_ID      ,
          CREATED_BY                  ,
          LAST_UPDATE_DATE            ,
          PROGRAM_ID                  ,
          PROGRAM_UPDATE_DATE         ,
          WH_UPDATE_DATE              ,
          ATTRIBUTE_CATEGORY          ,
          ATTRIBUTE1                  ,
          ATTRIBUTE2                  ,
          ATTRIBUTE3                  ,
          ATTRIBUTE4                  ,
          ATTRIBUTE5                  ,
          ATTRIBUTE6                  ,
          ATTRIBUTE7                  ,
          ATTRIBUTE8                  ,
          ATTRIBUTE9                  ,
          ATTRIBUTE10                 ,
          ATTRIBUTE11                 ,
          ATTRIBUTE12                 ,
          ATTRIBUTE13                 ,
          ATTRIBUTE14                 ,
          ATTRIBUTE15                 ,
          ATTRIBUTE16                 ,
          ATTRIBUTE17                 ,
          ATTRIBUTE18                 ,
          ATTRIBUTE19                 ,
          ATTRIBUTE20                 ,
          ATTRIBUTE21                 ,
          ATTRIBUTE22                 ,
          ATTRIBUTE23                 ,
          ATTRIBUTE24                 ,
          GLOBAL_ATTRIBUTE_CATEGORY   ,
          GLOBAL_ATTRIBUTE1           ,
          GLOBAL_ATTRIBUTE2           ,
          GLOBAL_ATTRIBUTE4           ,
          GLOBAL_ATTRIBUTE3           ,
          GLOBAL_ATTRIBUTE5           ,
          GLOBAL_ATTRIBUTE6           ,
          GLOBAL_ATTRIBUTE7           ,
          GLOBAL_ATTRIBUTE8           ,
          GLOBAL_ATTRIBUTE9           ,
          GLOBAL_ATTRIBUTE10          ,
          GLOBAL_ATTRIBUTE11          ,
          GLOBAL_ATTRIBUTE12          ,
          GLOBAL_ATTRIBUTE13          ,
          GLOBAL_ATTRIBUTE14          ,
          GLOBAL_ATTRIBUTE15          ,
          GLOBAL_ATTRIBUTE16          ,
          GLOBAL_ATTRIBUTE17          ,
          GLOBAL_ATTRIBUTE18          ,
          GLOBAL_ATTRIBUTE19          ,
          GLOBAL_ATTRIBUTE20          ,
          ORIG_SYSTEM_REFERENCE       ,
          SIC_CODE                    ,
          HQ_BRANCH_IND               ,
          CUSTOMER_KEY                ,
          TAX_REFERENCE               ,
          JGZZ_FISCAL_CODE            ,
          DUNS_NUMBER                 ,
          TAX_NAME                    ,
          PERSON_PRE_NAME_ADJUNCT     ,
          PERSON_FIRST_NAME           ,
          PERSON_MIDDLE_NAME          ,
          PERSON_LAST_NAME            ,
          PERSON_NAME_SUFFIX          ,
          PERSON_TITLE                ,
          PERSON_ACADEMIC_TITLE       ,
          PERSON_PREVIOUS_LAST_NAME   ,
          KNOWN_AS                    ,
          PERSON_IDEN_TYPE            ,
          PERSON_IDENTIFIER           ,
          GROUP_TYPE                  ,
          COUNTRY                     ,
          ADDRESS1                    ,
          ADDRESS2                    ,
          ADDRESS3                    ,
          ADDRESS4                    ,
          CITY                        ,
          POSTAL_CODE                 ,
          STATE                       ,
          PROVINCE                    ,
          STATUS                      ,
          COUNTY                      ,
          SIC_CODE_TYPE               ,
          TOTAL_NUM_OF_ORDERS         ,
          TOTAL_ORDERED_AMOUNT        ,
          LAST_ORDERED_DATE           ,
          URL                         ,
          EMAIL_ADDRESS               ,
          DO_NOT_MAIL_FLAG            ,
          ANALYSIS_FY                 ,
          FISCAL_YEAREND_MONTH        ,
          EMPLOYEES_TOTAL             ,
          CURR_FY_POTENTIAL_REVENUE   ,
          NEXT_FY_POTENTIAL_REVENUE   ,
          YEAR_ESTABLISHED            ,
          GSA_INDICATOR_FLAG          ,
          MISSION_STATEMENT           ,
          ORGANIZATION_NAME_PHONETIC  ,
          PERSON_FIRST_NAME_PHONETIC  ,
          PERSON_LAST_NAME_PHONETIC   ,
          LANGUAGE_NAME               ,
          CATEGORY_CODE               ,
          REFERENCE_USE_FLAG          ,
          THIRD_PARTY_FLAG            ,
          COMPETITOR_FLAG             ,
          SALUTATION                  ,
          KNOWN_AS2                   ,
          KNOWN_AS3                   ,
          KNOWN_AS4                   ,
          KNOWN_AS5                   ,
          DUNS_NUMBER_C               ,
          OBJECT_VERSION_NUMBER       ,
          CREATED_BY_MODULE           ,
          APPLICATION_ID               
       )
       values
       (
            l_party_id                      ,
            cur.PARTY_NUMBER                ,
            cur.PARTY_NAME                  ,
            cur.PARTY_TYPE                  ,
            cur.VALIDATED_FLAG              ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE               ,
            cur.LAST_UPDATE_LOGIN           ,
            cur.REQUEST_ID                  ,
            cur.PROGRAM_APPLICATION_ID      ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_DATE            ,
            cur.PROGRAM_ID                  ,
            cur.PROGRAM_UPDATE_DATE         ,
            cur.WH_UPDATE_DATE              ,
            cur.ATTRIBUTE_CATEGORY          ,
            cur.ATTRIBUTE1                  ,
            cur.ATTRIBUTE2                  ,
            cur.ATTRIBUTE3                  ,
            cur.ATTRIBUTE4                  ,
            cur.ATTRIBUTE5                  ,
            cur.ATTRIBUTE6                  ,
            cur.ATTRIBUTE7                  ,
            cur.ATTRIBUTE8                  ,
            cur.ATTRIBUTE9                  ,
            cur.ATTRIBUTE10                 ,
            cur.ATTRIBUTE11                 ,
            cur.ATTRIBUTE12                 ,
            cur.ATTRIBUTE13                 ,
            cur.ATTRIBUTE14                 ,
            cur.ATTRIBUTE15                 ,
            cur.ATTRIBUTE16                 ,
            cur.ATTRIBUTE17                 ,
            cur.ATTRIBUTE18                 ,
            cur.ATTRIBUTE19                 ,
            cur.ATTRIBUTE20                 ,
            cur.ATTRIBUTE21                 ,
            cur.ATTRIBUTE22                 ,
            cur.ATTRIBUTE23                 ,
            cur.ATTRIBUTE24                 ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY   ,
            cur.GLOBAL_ATTRIBUTE1           ,
            cur.GLOBAL_ATTRIBUTE2           ,
            cur.GLOBAL_ATTRIBUTE4           ,
            cur.GLOBAL_ATTRIBUTE3           ,
            cur.GLOBAL_ATTRIBUTE5           ,
            cur.GLOBAL_ATTRIBUTE6           ,
            cur.GLOBAL_ATTRIBUTE7           ,
            cur.GLOBAL_ATTRIBUTE8           ,
            cur.GLOBAL_ATTRIBUTE9           ,
            cur.GLOBAL_ATTRIBUTE10          ,
            cur.GLOBAL_ATTRIBUTE11          ,
            cur.GLOBAL_ATTRIBUTE12          ,
            cur.GLOBAL_ATTRIBUTE13          ,
            cur.GLOBAL_ATTRIBUTE14          ,
            cur.GLOBAL_ATTRIBUTE15          ,
            cur.GLOBAL_ATTRIBUTE16          ,
            cur.GLOBAL_ATTRIBUTE17          ,
            cur.GLOBAL_ATTRIBUTE18          ,
            cur.GLOBAL_ATTRIBUTE19          ,
            cur.GLOBAL_ATTRIBUTE20          ,
            cur.ORIG_SYSTEM_REFERENCE       ,
            cur.SIC_CODE                    ,
            cur.HQ_BRANCH_IND               ,
            cur.CUSTOMER_KEY                ,
            cur.TAX_REFERENCE               ,
            cur.JGZZ_FISCAL_CODE            ,
            cur.DUNS_NUMBER                 ,
            cur.TAX_NAME                    ,
            cur.PERSON_PRE_NAME_ADJUNCT     ,
            cur.PERSON_FIRST_NAME           ,
            cur.PERSON_MIDDLE_NAME          ,
            cur.PERSON_LAST_NAME            ,
            cur.PERSON_NAME_SUFFIX          ,
            cur.PERSON_TITLE                ,
            cur.PERSON_ACADEMIC_TITLE       ,
            cur.PERSON_PREVIOUS_LAST_NAME   ,
            cur.KNOWN_AS                    ,
            cur.PERSON_IDEN_TYPE            ,
            cur.PERSON_IDENTIFIER           ,
            cur.GROUP_TYPE                  ,
            cur.COUNTRY                     ,
            cur.ADDRESS1                    ,
            cur.ADDRESS2                    ,
            cur.ADDRESS3                    ,
            cur.ADDRESS4                    ,
            cur.CITY                        ,
            cur.POSTAL_CODE                 ,
            cur.STATE                       ,
            cur.PROVINCE                    ,
            cur.STATUS                      ,
            cur.COUNTY                      ,
            cur.SIC_CODE_TYPE               ,
            cur.TOTAL_NUM_OF_ORDERS         ,
            cur.TOTAL_ORDERED_AMOUNT        ,
            cur.LAST_ORDERED_DATE           ,
            cur.URL                         ,
            cur.EMAIL_ADDRESS               ,
            cur.DO_NOT_MAIL_FLAG            ,
            cur.ANALYSIS_FY                 ,
            cur.FISCAL_YEAREND_MONTH        ,
            cur.EMPLOYEES_TOTAL             ,
            cur.CURR_FY_POTENTIAL_REVENUE   ,
            cur.NEXT_FY_POTENTIAL_REVENUE   ,
            cur.YEAR_ESTABLISHED            ,
            cur.GSA_INDICATOR_FLAG          ,
            cur.MISSION_STATEMENT           ,
            cur.ORGANIZATION_NAME_PHONETIC  ,
            cur.PERSON_FIRST_NAME_PHONETIC  ,
            cur.PERSON_LAST_NAME_PHONETIC   ,
            cur.LANGUAGE_NAME               ,
            cur.CATEGORY_CODE               ,
            cur.REFERENCE_USE_FLAG          ,
            cur.THIRD_PARTY_FLAG            ,
            cur.COMPETITOR_FLAG             ,
            cur.SALUTATION                  ,
            cur.KNOWN_AS2                   ,
            cur.KNOWN_AS3                   ,
            cur.KNOWN_AS4                   ,
            cur.KNOWN_AS5                   ,
            cur.DUNS_NUMBER_C               ,
            cur.OBJECT_VERSION_NUMBER       ,
            cur.CREATED_BY_MODULE           ,
            cur.APPLICATION_ID                                
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   	 
   	 update chz_parties hp1
   	 set    s_org_id = (select CAPBRIMP_MAPPING.GetOrgMapping(hp2.s_org_id)
	                    from   crst_hz_parties chp2, chz_parties hp2
						where  chp2.party_id_n = hp1.party_id
						and    chp2.party_id = hp2.party_id),
   	        s_business_group_id = (select CAPBRIMP_MAPPING.GetBGMapping(hp2.s_business_group_id)
	                               from   crst_hz_parties chp2, chz_parties hp2
							       where  chp2.party_id_n = hp1.party_id
						           and    chp2.party_id = hp2.party_id)
   	 where  exists (select 'X'
   	                from   crst_hz_parties chp1
   	                where  chp1.party_id_n = hp1.party_id);
   	 
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_PARTY', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
